﻿using Maxi.Areas.Importador.Models;
using Maxi.Controllers;
using Maxi.Models;
using Maxi.Models.Banco;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Web;
using System.Web.Mvc;

namespace Maxi.Areas.Importador.Controllers
{
    //[RegisterLog]
    public class ImportadorController : BaseController
    {
        // GET: Importador/Importador
        public ActionResult Index()
        {
            return View();
        }


        public JsonResult IniciarImportacao()
        {
            Stopwatch tempo = new Stopwatch();
            tempo.Start();

            ImportarAlunos();
            ImportarResponsaveis();
            ImportarProfessores();
            ImportarDisciplinas();
            ImportarResponsavelAluno();
            ImportarTurmas();
            ImportarAlunoTurma();
            ImportarTurmaDisciplina();
            ImportarProfessorDisciplina();
            ImportarTiposOcorrencia();
            ImportarOcorrencias();
            ImportarExtratoFinanceiro();
            ImportarBoletins();
            ImportarNotas();
            ImportarFrequencia();

            tempo.Stop();
            return Json(new { tempo = tempo.Elapsed.ToString() }, JsonRequestBehavior.AllowGet);
        }

        public ActionResult Importar()
        {
            return View("ImportarExtratoFinanceiro");
        }

        public void ImportarExtratoFinanceiro()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<ResponsaveisExtratoFinanceiro> list = imp.ResponsaveisExtratoFinanceiro.Where(x => x.flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_Responsaveis_Extrato_Financeiro target = new Tb_Responsaveis_Extrato_Financeiro();

                target.lancamento = item.lancamento;
                target.anoLetivo = item.anoLetivo;
                target.codAluno = item.codAluno;
                target.dtCancelamento = item.dtCancelamento;
                target.dtPagamento = item.dtPagamento;
                target.dtVencimento = item.dtVencimento;
                target.nomeRespFin = item.nomeRespFin;
                target.numDocumento = item.numDocumento;
                target.observacao = item.observacao;
                target.situacao = item.situacao;
                target.strReferencia = item.strReferencia;
                target.valorAPagar = item.valorAPagar;
                target.valorDescJuros = item.valorDescJuros;
                target.valorOriginal = item.valorOriginal;
                target.valorPago = item.valorPago;
                target.ImportadoEm = DateTime.Now;

                item.flag = "I";

                if (con.Tb_Responsaveis_Extrato_Financeiro.Any(x => x.numDocumento == target.numDocumento))
                {
                    con.Tb_Responsaveis_Extrato_Financeiro.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    con.Tb_Responsaveis_Extrato_Financeiro.Add(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Added;
                }


                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
                count++;
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarDisciplinas()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<Disciplina> list = imp.Disciplina.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_Disciplina target = new Tb_Disciplina();

                target.CodDisciplina = item.CodDisciplina;
                target.Nome = item.NomeDisciplina;
                target.ImportadoEm = DateTime.Now;

                item.Flag = "I";

                if (con.Tb_Disciplina.Any(x => x.CodDisciplina == target.CodDisciplina))
                {
                    con.Tb_Disciplina.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    con.Tb_Disciplina.Add(target);
                }

                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarTurmas()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<Turma> list = imp.Turma.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_Turma target = new Tb_Turma();

                target.AnoLetivo = item.AnoLetivo;
                target.CodTurma = item.CodTurma;
                target.Descricao = item.Descricao;
                target.Serie = item.Serie;
                target.Turno = item.Turno;
                target.ImportadoEm = DateTime.Now;

                item.Flag = "I";

                if (con.Tb_Turma.Any(x => x.CodTurma == target.CodTurma))
                {
                    con.Tb_Turma.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    con.Tb_Turma.Add(target);
                }

                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarAlunos()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<Aluno> list = imp.Aluno.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_Usuario target = new Tb_Usuario();

                target.Id = item.CodAluno + Extensions.SuAluno;
                target.Nome = item.Nome;
                target.Usuario = GerarUsuario(item.Nome.Trim());
                target.Bairro = item.Bairro;
                target.Cep = item.Cep;
                target.Cidade = item.Cidade;
                target.Uf = item.Uf;
                target.Endereco = item.Endereco;
                target.DddFixo = item.DddFixo;
                target.FoneFixo = item.FoneFixo;
                target.Email = item.Email == null || item.Email == string.Empty ? string.Empty : item.Email;
                target.TipoUsuario = "A";
                target.PrimeiroLogin = false;
                target.IdGrupo = 2;
                target.ImportadoEm = DateTime.Now;
                target.Tb_Aluno = new Tb_Aluno() { UsuarioId = item.CodAluno + Extensions.SuAluno, DataNascimento = item.DataNascimento, ImportadoEm = DateTime.Now };

                item.Flag = "I";

                if (con.Tb_Usuario.Any(x => (x.Id + Extensions.SuAluno).Equals( target.Id)))
                {
                    con.Tb_Usuario.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                    con.Entry(target.Tb_Aluno).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    target.Senha = Extensions.GerarSenha();
                    con.Tb_Usuario.Add(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Added;
                    con.Entry(target.Tb_Aluno).State = System.Data.Entity.EntityState.Added;
                }

                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
                count++;
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarProfessores()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<Professor> list = imp.Professor.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                count++;
                Tb_Usuario target = new Tb_Usuario();

                target.Id = item.CodProfessor.ToString() + Extensions.SuProfessor;
                target.Nome = item.Nome;
                target.Usuario = GerarUsuario(item.Nome.Trim());
                target.Bairro = item.Bairro;
                target.Cep = item.Cep;
                target.Cidade = item.Cidade;
                target.Uf = item.Estado;
                target.Endereco = item.Endereco;
                target.FoneFixo = item.Fone;
                target.Celular = item.Celular;
                target.Email = item.Email == null || item.Email == string.Empty ? string.Empty : item.Email;
                target.TipoUsuario = "P";
                target.IdGrupo = 4;
                target.PrimeiroLogin = false;
                target.ImportadoEm = DateTime.Now;

                target.Tb_Professor = new Tb_Professor()
                {
                    IdUsuario = item.CodProfessor + Extensions.SuProfessor,
                    Tipo = item.Tipo,
                    TipoColaborador = item.TipoColaborador,
                    Apelido = item.Apelido,
                    Rg = item.Rg,
                    RgData = item.Rg_Data,
                    RgOrgExp = item.Rg_Org_Exp,
                    Cpf = item.Cpf,
                    DtCancelamento = item.DtCancelamento,
                    ImportadoEm = DateTime.Now
                };

                item.Flag = "I";

                if (con.Tb_Usuario.Any(x => x.Id + Extensions.SuProfessor == target.Id))
                {
                    con.Tb_Usuario.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                    con.Entry(target.Tb_Professor).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    target.Senha = Extensions.GerarSenha();
                    con.Tb_Usuario.Add(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Added;
                    con.Entry(target.Tb_Professor).State = System.Data.Entity.EntityState.Added;
                }

                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }

            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();

        }

        public void ImportarResponsaveis()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<Responsavel> list = imp.Responsavel.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_Usuario target = new Tb_Usuario();

                target.Id = item.CodResp + Extensions.SuResponsavel;
                target.Nome = item.Nome;
                target.Usuario = GerarUsuario(item.Nome.Trim());
                target.Bairro = item.Bairro;
                target.Cep = item.Cep;
                target.Cidade = item.Cidade;
                target.Uf = item.Uf;
                target.Endereco = item.Endereco;
                target.DddFixo = item.DddFixo;
                target.FoneFixo = item.FoneFixo;
                target.Email = item.Email == null || item.Email == string.Empty ? string.Empty : item.Email;
                target.TipoUsuario = "R";
                target.IdGrupo = 3;
                target.PrimeiroLogin = false;
                target.Tb_Responsavel = new Tb_Responsavel() { IdUsuario = item.CodResp + Extensions.SuResponsavel, Cpf = item.Cpf, ImportadoEm = DateTime.Now };
                target.ImportadoEm = DateTime.Now;

                item.Flag = "I";

                if (con.Tb_Usuario.Any(x => x.Id + Extensions.SuResponsavel == target.Id))
                {
                    con.Tb_Usuario.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                    con.Entry(target.Tb_Responsavel).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    target.Senha = Extensions.GerarSenha();
                    con.Tb_Usuario.Add(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Added;
                }

                if (count > 1000)
                {
                    target.Senha = Extensions.GerarSenha();
                    con.Database.Log = Console.WriteLine;
                    Console.WriteLine(con.SaveChanges());
                    imp.SaveChanges();
                    count = 0;
                }
                count++;
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarResponsavelAluno()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<ResponsavelAluno> list = imp.ResponsavelAluno.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_ResponsavelAluno target = new Tb_ResponsavelAluno();


                target.IdAluno = item.Aluno.CodAluno + Extensions.SuAluno;
                target.IdResponsavel = item.CodResp + Extensions.SuResponsavel;
                target.ImportadoEm = DateTime.Now;

                item.Flag = "I";

                if (con.Tb_ResponsavelAluno.Any(x => x.IdResponsavel == item.CodResp + Extensions.SuResponsavel && x.IdAluno == item.CodAluno + Extensions.SuAluno))
                {
                    con.Tb_ResponsavelAluno.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    con.Tb_ResponsavelAluno.Add(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Added;
                }
                string lastResp = item.CodResp;
                string lastAluno = item.CodAluno.ToString();
                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
                count++;
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarAlunoTurma()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<AlunoTurma> list = imp.AlunoTurma.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_AlunoTurma target = new Tb_AlunoTurma();


                target.IdAluno = item.Aluno.CodAluno + Extensions.SuAluno;
                target.IdTurma = item.CodTurma;
                target.ImportadoEm = DateTime.Now;

                item.Flag = "I";

                if (con.Tb_AlunoTurma.Any(x => x.IdTurma == target.IdTurma && x.IdAluno == target.IdAluno))
                {
                    con.Tb_AlunoTurma.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    con.Tb_AlunoTurma.Add(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Added;
                }

                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
                count++;
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarTurmaDisciplina()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<TurmaDisciplina> list = imp.TurmaDisciplina.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_TurmaDisciplina target = new Tb_TurmaDisciplina();

                target.CodDisciplina = item.CodDisciplina;
                target.CodTurma = item.CodTurma;
                target.ImportadoEm = DateTime.Now;

                item.Flag = "I";

                if (con.Tb_TurmaDisciplina.Any(x => x.CodDisciplina == item.CodDisciplina && x.CodTurma == item.CodTurma))
                {
                    con.Tb_TurmaDisciplina.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    con.Tb_TurmaDisciplina.Add(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Added;
                }

                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
                count++;
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarProfessorDisciplina()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<ProfessorDisciplina> list = imp.ProfessorDisciplina.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_ProfessorDisciplina target = new Tb_ProfessorDisciplina();

                target.CodDisciplina = item.CodDisciplina;
                target.CodProfessor = item.CodProfessor + Extensions.SuProfessor;
                target.ImportadoEm = DateTime.Now;


                item.Flag = "I";

                if (con.Tb_ProfessorDisciplina.Any(x => x.CodDisciplina == target.CodDisciplina && x.CodProfessor == target.CodProfessor))
                {
                    con.Tb_ProfessorDisciplina.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    con.Tb_ProfessorDisciplina.Add(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Added;
                }

                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
                count++;
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarNotas()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<Nota> list = imp.Nota.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_Nota target = new Tb_Nota();

                target.Cod = item.Cod;
                target.AnoLetivo = item.AnoLetivo;
                target.Bimestre = item.Bimestre;
                target.CodAluno = item.CodAluno + Extensions.SuAluno;
                target.CodAvaliacao = item.CodAvaliacao;
                target.CodEtapa = item.CodEtapa;
                target.DataAvaliacao = item.DataAvaliacao;
                target.NomeAvaliacao = item.NomeAvaliacao;
                target.NotaConceito = item.NotaConceito;
                target.Observacao = item.Observacao;
                target.Semestre = item.Semestre;
                target.CodDisciplina = item.CodDisciplina;
                target.ImportadoEm = DateTime.Now;

                item.Flag = "I";

                if (con.Tb_Nota.Any(x => x.Cod == target.Cod))
                {
                    con.Tb_Nota.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    con.Tb_Nota.Add(target);
                    //con.Entry(target).State = System.Data.Entity.EntityState.Added;
                }

                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
                count++;
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarBoletins()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<Boletim> list = imp.Boletim.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_Boletim target = new Tb_Boletim();

                target.Cod = item.Cod;
                target.AnoLetivo = item.AnoLetivo;
                target.Bimestre = item.Bimestre;
                target.CodAluno = item.CodAluno + Extensions.SuAluno;
                target.Semestre = item.Semestre;
                target.CodDisciplina = item.CodDisciplina;
                target.CodCampoBoletim = item.CodCampoBoletim;
                target.NotaConceito = item.NotaConceito;
                target.NotaConceitoValor = item.NotaConceitoValor;
                target.ImportadoEm = DateTime.Now;

                item.Flag = "I";

                if (con.Tb_Boletim.Any(x => x.Cod == target.Cod))
                {
                    con.Tb_Boletim.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    con.Tb_Boletim.Add(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Added;
                }

                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
                count++;
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarTiposOcorrencia()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<TipoOcorrencia> list = imp.TipoOcorrencia.ToList();

            foreach (var item in list)
            {
                Tb_TipoOcorrencia target = new Tb_TipoOcorrencia();

                target.Codigo = item.Codigo;
                target.Cor = item.Cor;
                target.Interno = item.Interno;
                target.Nome = item.Nome;
                target.ImportadoEm = DateTime.Now;


                if (con.Tb_TipoOcorrencia.Any(x => x.Codigo == item.Codigo))
                {
                    con.Tb_TipoOcorrencia.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    con.Tb_TipoOcorrencia.Add(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Added;
                }

                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
                count++;
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarOcorrencias()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<Ocorrencia> list = imp.Ocorrencia.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_Ocorrencia target = new Tb_Ocorrencia();

                target.Cod = item.Cod;
                target.CodAluno = item.CodAluno + Extensions.SuAluno;
                target.CodDisciplina = item.CodDisciplina;
                target.CodOcorrencia = item.CodOcorrencia;
                target.Complemento = item.Complemento;
                target.Data = item.Data;
                target.Descricao = item.Descricao;
                target.NumOrdem = item.NumOrdem;
                target.Tipo = item.Tipo;

                item.Flag = "I";


                if (con.Tb_Ocorrencia.Any(x => x.Cod == item.Cod))
                {
                    con.Tb_Ocorrencia.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    con.Tb_Ocorrencia.Add(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Added;
                }

                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
                count++;
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();
        }

        public void ImportarFrequencia()
        {
            Conexao con = new Conexao();
            Importacao imp = new Importacao();

            con.Configuration.AutoDetectChangesEnabled = false;
            con.Configuration.ValidateOnSaveEnabled = false;
            int count = 0;
            List<Frequencia> list = imp.Frequencia.Where(x => x.Flag != "I").ToList();

            foreach (var item in list)
            {
                Tb_Frequencia target = new Tb_Frequencia();

                target.Cod = item.Cod;
                target.CodAluno = item.CodAluno + Extensions.SuAluno;
                target.CodDisciplina = item.CodDisciplina;
                target.AnoLetivo = item.AnoLetivo;
                target.AulasDadas = item.AulasDadas;
                target.CodEtapa = item.CodEtapa;
                target.DescEtapa = item.DescEtapa;
                target.Faltas = item.Faltas;
                target.Observacao = item.Observacao;
                target.PercFaltas = item.PercFaltas;
                target.PercPresencas = item.PercPresencas;
                target.Presencas = item.Presencas;
                target.ImportadoEm = DateTime.Now;


                item.Flag = "I";

                if (con.Tb_Frequencia.Any(x => x.Cod == target.Cod))
                {
                    con.Tb_Frequencia.Attach(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Modified;
                }
                else
                {
                    con.Tb_Frequencia.Add(target);
                    con.Entry(target).State = System.Data.Entity.EntityState.Added;
                }

                if (count > 1000)
                {
                    con.SaveChanges();
                    imp.SaveChanges();
                    count = 0;
                }
                count++;
            }
            con.SaveChanges();
            imp.SaveChanges();
            con.Dispose();
            imp.Dispose();

        }



        private string GerarUsuario(String nome)
        {
            String[] v = nome.Split(' ');
            Random rnd = new Random();
            String login = v[0].ToLower().RemoverAcento() + rnd.Next(10000, 99999).ToString();
            return login;
        }
    }
}